using System;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace PoliceBase
{
    public class DBBasic
    {
        #region DataFields
        protected string _connectionUrl;
        protected SqlConnection _sCon;
        protected SqlCommand _sCom;
        protected SqlDataReader _sdr;
        #endregion

        #region Properties

        #endregion

        #region Constructors
        public DBBasic(string connectionString)
        {
            this._connectionUrl = connectionString;
            this._sCon = new SqlConnection(this._connectionUrl);
            this._sCom = new SqlCommand();
            this._sCom.Connection = this._sCon;
        }
        #endregion

        #region PublicMethods
        //Nodes
        public Node[] LoadAllNodes()
        {
            Node[] arr = null;
            try
            {
                this._sCom.CommandText = "SELECT Count(*) FROM Nodes";
                this._sCon.Open();
                int i = Convert.ToInt32(this._sCom.ExecuteScalar());
                arr = new Node[i + 1];

                this._sCom.CommandText = "SELECT XPosition,YPosition,NodeID FROM Nodes";
                this._sdr = this._sCom.ExecuteReader();

                while (this._sdr.Read())
                    arr[this._sdr.GetInt32(2)] = new Node(this._sdr.GetInt32(0), this._sdr.GetInt32(1), this._sdr.GetInt32(2));
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return arr;
        }
        public Node[] LoadSurroundingNodes(Node node)
        {
            return LoadSurroundingNodes(node.ID);
        }
        public Node[] LoadSurroundingNodes(int nodeID)
        {
            Node[] arr = null;
            try
            {
                string sqlStmt = "SELECT FromNodeID FROM Paths WHERE (ToNodeID = " + nodeID + " )";
                sqlStmt += " UNION ";
                sqlStmt += "SELECT ToNodeID FROM Paths WHERE (FromNodeID = " + nodeID + ")";

                this._sCom.CommandText = sqlStmt;
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();
                
                List<int> li = new List<int>();
                while (this._sdr.Read())
                    li.Add(this._sdr.GetInt32(0));

                this._sdr.Close();
                this._sCon.Close();

                arr = new Node[li.Count];
                for (int i = 0; i < arr.Length; i++)
                    arr[i] = LoadNode(li[i]);
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return arr;
        }
        public Node LoadNode(int ID)
        {
            Node nd = null;
            try
            {
                this._sCom.CommandText = "SELECT XPosition, YPosition FROM Nodes WHERE NodeID = " + ID;
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();

                this._sdr.Read();
                int x = this._sdr.GetInt32(0);
                int y = this._sdr.GetInt32(1);
                nd = new Node(x, y, ID);
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return nd;
        }

        //Map dimensions
        public int GetMaxX()
        {
            int res = -1;
            try
            {
                this._sCom.CommandText = "SELECT Max(XPosition) FROM Nodes";
                this._sCon.Open();
                res = Convert.ToInt32(this._sCom.ExecuteScalar());
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public int GetMaxY()
        {
            int res = -1;
            try
            {
                this._sCom.CommandText = "SELECT Max(YPosition) FROM Nodes";
                this._sCon.Open();
                res = Convert.ToInt32(this._sCom.ExecuteScalar());
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }

        //Paths
        public int GetPathID(Address add)
        {
            int res = -1;
            try
            {
                int b = add.Number;
                if (b % 2 == 1)
                    this._sCom.CommandText = "SELECT StartingOddBuilding,EndingOddBuilding,PathID FROM Paths WHERE (Street = '" + add.Street + "' AND Subarb = '" + add.Subarb + "')";
                else
                    this._sCom.CommandText = "SELECT StartingEvenBuilding,EndingEvenBuilding,PathID FROM Paths WHERE (Street = '" + add.Street + "' AND Subarb = '" + add.Subarb + "')";
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();

                int i1, i2;
                while (this._sdr.Read())
                {
                    i1 = this._sdr.GetInt32(0);
                    i2 = this._sdr.GetInt32(1);
                    if (((b >= i1) && (b <= i2)) || ((b <= i1) && (b >= i2)))
                    {
                        res = this._sdr.GetInt32(2);
                        break;
                    }
                }
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public Path GetPath(Address add)
        {
            return LoadPath(GetPathID(add));
        }
        public Path LoadPath(int PathID)
        {
            Path res = null;
            if (PathID > 0)
            {
                try
                {
                    this._sCom.CommandText = "SELECT FromNodeID, ToNodeID, StartingOddBuilding, StartingEvenBuilding, EndingOddBuilding, EndingEvenBuilding, Street, Subarb,  Cost FROM Paths WHERE PathID = " + PathID;
                    this._sCon.Open();
                    this._sdr = this._sCom.ExecuteReader();
                    this._sdr.Read();
                    int fromNodeID = this._sdr.GetInt32(0);
                    int toNodeID = this._sdr.GetInt32(1);
                    int startingOdd = this._sdr.GetInt32(2);
                    int startingEven = this._sdr.GetInt32(3);
                    int endingOdd = this._sdr.GetInt32(4);
                    int endingEven = this._sdr.GetInt32(5);
                    string street = this._sdr.GetString(6);
                    string subarb = this._sdr.GetString(7);
                    int cost = this._sdr.GetInt32(8);
                    this._sdr.Close();
                    this._sCon.Close();
                    Node start = LoadNode(fromNodeID);
                    Node end = LoadNode(toNodeID);
                    res = new Path(PathID, start, end, startingOdd, startingEven, endingOdd, endingEven, street, subarb, cost);
                }
                catch (Exception e)
                {
                    LogError(e.Message + "---" + e.StackTrace);
                }
                finally
                {
                    try { this._sdr.Close(); }
                    catch (Exception) { }
                    try { this._sCon.Close(); }
                    catch (Exception) { }
                } 
            }
            return res;
        }
        public Path[] LaodAllPaths()
        {
            Path[] res = null;
            try
            {
                this._sCom.CommandText = "SELECT Count(*) FROM Paths";
                this._sCon.Open();
                int i = Convert.ToInt32(this._sCom.ExecuteScalar());
                res = new Path[i + 1];
                this._sCon.Close();

                for (int x = 1; x < i + 1; x++)
                    res[x] = LoadPath(x);
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }

        //Getting Costs
        public int[] TrasformAddress(Address add)
        {
            int[] arr = null;
            arr = new int[3];
            Node nd = GetPosition(add);
            arr[0] = nd.X;
            arr[1] = nd.Y;
            arr[2] = GetPathID(add);
            return arr;
        }
        public int GetCost(int Node1ID, int Node2ID)
        {
            int cost = -1;
            try
            {
                this._sCom.CommandText = "SELECT Cost FROM Paths WHERE (FromNodeID = " + Node1ID + " AND ToNodeID = " + Node2ID + " ) OR (FromNodeID = " + Node2ID + " AND ToNodeID = " + Node1ID + " )";
                this._sCon.Open();
                cost = Convert.ToInt32(this._sCom.ExecuteScalar());
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return cost;
        }
        public int GetCost(Node Node1, Node Node2)
        {
            return GetCost(Node1.ID, Node2.ID);
        }
        public int GetCost(int x, int y, int pieceID, int startNodeID)
        {
            int res = -1;
            try
            {
                this._sCom.CommandText = "SELECT Cost, FromNodeID, ToNodeID FROM Paths WHERE PathID = " + pieceID;
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();
                this._sdr.Read();
                int cost = this._sdr.GetInt32(0);
                int st = this._sdr.GetInt32(1);
                int ed = this._sdr.GetInt32(2);
                this._sdr.Close();
                this._sCon.Close();

                Node stNd = LoadNode(st);
                Node edNd = LoadNode(ed);

                this._sCom.CommandText = "SELECT XPosition, YPosition FROM Nodes WHERE NodeID = " + startNodeID;
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();
                this._sdr.Read();
                double l = Node.GetDistance(new Node(this._sdr.GetInt32(0), this._sdr.GetInt32(1)), new Node(x, y));
                double d = Node.GetDistance(stNd, edNd);
                res = (int)(cost * l / d);
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public int GetCost(Address add, int startNodeID)
        {
            int[] arr = TrasformAddress(add);
            return GetCost(arr[0], arr[1], arr[2], startNodeID);
        }

        //Dealing with cars
        public Node[] LoadCarsPositions()
        {
            Node[] arr = null;
            try
            {
                this._sCom.CommandText = "SELECT Count(*) FROM Cars";
                this._sCon.Open();
                int n = Convert.ToInt32(this._sCom.ExecuteScalar());
                arr = new Node[n + 1];

                this._sCom.CommandText = "SELECT CarID,XPosition,YPosition FROM Cars ORDER BY CarID";
                this._sdr = this._sCom.ExecuteReader();

                while (this._sdr.Read())
                    arr[this._sdr.GetInt32(0)] = new Node(this._sdr.GetInt32(1), this._sdr.GetInt32(2));
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return arr;

        }
        public Node LoadCarPosition(int CarID)
        {
            Node res = null;
            try
            {
                this._sCom.CommandText = "SELECT XPosition,YPosition FROM Cars WHERE CarID = " + CarID;
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();
                this._sdr.Read();

                res = new Node(this._sdr.GetInt32(0), this._sdr.GetInt32(1));
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        
        public bool GetCarStatus(int carID)
        {
            bool res = false;
            try
            {
                this._sCom.CommandText = "SELECT State FROM Cars WHERE CarID = " + carID;
                this._sCon.Open();
                res = (Convert.ToInt32(this._sCom.ExecuteScalar()) != 0);
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public bool UpdateCarPosition(int carID, Node position)
        {
            bool res = false;
            try
            {
                this._sCom.CommandText = "UPDATE Cars SET XPosition = " + position.X + ", YPosition = " + position.Y + " WHERE CarID = " + carID;
                this._sCon.Open();
                int i = this._sCom.ExecuteNonQuery();

                res = (i == 1);
            }
            catch (Exception)
            {
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public bool UpdateCarPosition(int carID, Node position, int PathID)
        {
            bool res = false;
            try
            {
                this._sCom.CommandText = "UPDATE Cars SET XPosition = " + position.X + ", YPosition = " + position.Y + ", PathID = " + PathID + " WHERE CarID = " + carID;
                this._sCon.Open();
                int i = this._sCom.ExecuteNonQuery();

                res = (i == 1);
            }
            catch (Exception)
            {
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public bool UpdateCarStatus(int carID, bool status)
        {
            bool res = false;
            try
            {
                this._sCom.CommandText = "UPDATE Cars SET State = " + ((status)? "1" : "0") + " WHERE CarID = " + carID;
                this._sCon.Open();
                int i = this._sCom.ExecuteNonQuery();

                res = (i == 1);
            }
            catch (Exception)
            {
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public int GetIncID(int carID)
        {
            int res = -1;
            try
            {
                this._sCom.CommandText = "SELECT IncidentID FROM Cars WHERE CarID = " + carID;
                this._sCon.Open();
                res = Convert.ToInt32(this._sCom.ExecuteScalar());
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public void ClearTarget(int carID)
        {
            try
            {
                this._sCom.CommandText = "UPDATE Cars SET IncidentID = -1 WHERE CarID = " + carID;
                this._sCon.Open();
                this._sCom.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
        }

        public void AllocateCars(int incidentID)
        {
            Incident inc = LoadIncident(incidentID);
            Node nd = GetPosition(inc.Address);
            int x = nd.X;
            int y = nd.Y;
            try
            {
                this._sCom.CommandText = "EXEC [dbo].[AllocateCars] @X = " + x.ToString() + " ,@Y = " + y.ToString() + " ,@IncID = " + incidentID.ToString();
                this._sCon.Open();
                this._sCom.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                this._sCom.CommandType = System.Data.CommandType.Text;
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                this._sCom.CommandType = System.Data.CommandType.Text;
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
        }

        //Addres validation
        public bool validateAddress(Address add)
        {
            return (GetPathID(add) >= 0);
        }

        //Delaing with incidents
        public int StoreIncident(Incident inc)
        {
            int res = -1;
            try
            {
                string sqlStmt = "";
                sqlStmt = "INSERT INTO Incidents(Injuries, Vectims, Fire, Address_No, Address_Street, Address_Subarb, IncidentType, Param1, Param2, Reporter_Name, Reporter_ID, Date, ReportingDate, Covered) VALUES (";
                sqlStmt += ((inc.Injuries) ? 1 : 0) + ",";
                sqlStmt += inc.Victims + ",";
                sqlStmt += ((inc.Fire) ? 1 : 0) + ",";
                sqlStmt += inc.Address.Number + ",";
                sqlStmt += "'" + inc.Address.Street + "',";
                sqlStmt += "'" + inc.Address.Subarb + "',";
                sqlStmt += "'" + inc.Type + "',";
                sqlStmt += inc.ParamA + ",";
                sqlStmt += inc.ParamB + ",";
                sqlStmt += "'" + inc.ReporterName + "',";
                sqlStmt += inc.ReporterID + ",";
                sqlStmt += "@date,"; // Put date
                sqlStmt += "@reportingDate,"; // Put reportingDate            
                sqlStmt += ((inc.Coverd) ? 1 : 0) + ") SELECT SCOPE_IDENTITY()";
                this._sCom.CommandText = sqlStmt;
                this._sCom.Parameters.AddWithValue("@date",inc.Date);
                this._sCom.Parameters.AddWithValue("@reportingDate", inc.ReportingDate);
                this._sCon.Open();
                res = Convert.ToInt32(this._sCom.ExecuteScalar());
                this._sCom.Parameters.Clear();
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                this._sCom.Parameters.Clear();
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public Incident LoadIncident(int incidentID)
        {
            Incident res = null;
            try
            {
                this._sCom.CommandText = "SELECT IncidentID, Injuries, Vectims, Fire, Address_No, Address_Street, Address_Subarb, IncidentType, Param1, Param2, Reporter_Name, Reporter_ID, Date, ReportingDate, Covered FROM Incidents WHERE IncidentID = " + incidentID;
                //                                  0           1       2         3     4           5               6               7               8       9       10              11        12    13              14
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();
                this._sdr.Read();
                res = ParseIncident(this._sdr);
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public Incident[] LoadAllIncidents()
        {
            List<Incident> arr = new List<Incident>();
            try
            {
                this._sCom.CommandText = "SELECT IncidentID, Injuries, Vectims, Fire, Address_No, Address_Street, Address_Subarb, IncidentType, Param1, Param2, Reporter_Name, Reporter_ID, Date, ReportingDate, Covered FROM Incidents";
                //                                  0           1       2         3     4           5               6               7               8       9       10              11        12    13              14
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();
                while (this._sdr.Read())
                    arr.Add(ParseIncident(this._sdr));
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return arr.ToArray();
        }
        public bool SetIncidentCovered(int IncidentID)
        {
            bool res = false;
            try
            {
                this._sCom.CommandText = "UPDATE Incidents SET Covered = 1 WHERE IncidentID = " + IncidentID;
                this._sCon.Open();
                int i = this._sCom.ExecuteNonQuery();

                res = (i == 1);
            }
            catch (Exception)
            {
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }

        //GUI's needs
        public string[] GetCrimeTypes()
        {
            List<string> ls = new List<string>();
            try
            {
                this._sCom.CommandText = "SELECT DISTINCT CrimeType FROM Crimes";
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();

                while (this._sdr.Read())
                    ls.Add(this._sdr.GetString(0));
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return ls.ToArray();
        }
        public string[] GetParamsNames(string CrimeType)
        {
            string[] res = null;
            try
            {
                this._sCom.CommandText = "SELECT Param1, Param2 FROM Crimes WHERE CrimeType = '" + CrimeType + "'";
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();

                this._sdr.Read();
                res = new string[2];
                res[0] = this._sdr.GetString(0);
                res[1] = this._sdr.GetString(1);
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public string[] GetStreetsNames()
        {
            List<string> ls = new List<string>();
            try
            {
                this._sCom.CommandText = "SELECT DISTINCT Street FROM Paths";
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();

                while (this._sdr.Read())
                    ls.Add(this._sdr.GetString(0));
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return ls.ToArray();
        }
        public string[] GetSubarbsNames()
        {
            List<string> ls = new List<string>();
            try
            {
                this._sCom.CommandText = "SELECT DISTINCT Subarb FROM Paths";
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();

                while (this._sdr.Read())
                    ls.Add(this._sdr.GetString(0));
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return ls.ToArray();
        }

        //Extra
        public int GetHealthyCars()
        {
            int res = -1;
            try
            {
                this._sCom.CommandText = "SELECT Count(*) FROM Cars WHERE (State > 0)";
                this._sCon.Open();
                res = Convert.ToInt32(this._sCom.ExecuteScalar());
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public int GetCoveredIncidents()
        {
            int res = -1;
            try
            {
                this._sCom.CommandText = "SELECT Count(*) FROM Incidents WHERE (Covered = 1)";
                this._sCon.Open();
                res = Convert.ToInt32(this._sCom.ExecuteScalar());
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }
        public int GetTotalIncidents()
        {
            int res = -1;
            try
            {
                this._sCom.CommandText = "SELECT Count(*) FROM Incidents";
                this._sCon.Open();
                res = Convert.ToInt32(this._sCom.ExecuteScalar());
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;
        }


        public Node[] GetShortestPath(Node CarsPosition, int CarsPathID, int IncidentID)
        {
            Node[] arr = null;
            try
            {
                Address add = LoadIncident(IncidentID).Address;
                Path IncidentPath = GetPath(add);
                Path CarPath = LoadPath(CarsPathID);

                Node inc = GetPosition(add);

                if (IncidentPath.ID == CarPath.ID)
                {
                    return new Node[] { CarsPosition, inc };
                }

                int incStId, incEdId, carStId, carEdId;
                incStId = IncidentPath.StatingNode.ID;
                incEdId = IncidentPath.EndingNode.ID;
                carStId = CarPath.StatingNode.ID;
                carEdId = CarPath.EndingNode.ID;

                if (carStId == incStId || carStId == incEdId)
                {
                    return new Node[] { CarsPosition, CarPath.StatingNode, inc };
                }

                if (carEdId == incStId || carEdId == incEdId)
                {
                    return new Node[] { CarsPosition, CarPath.EndingNode, inc };
                }

                int FromIncToStart = (int)Node.GetDistance(inc, IncidentPath.StatingNode);
                int FromIncToEnd = (int)Node.GetDistance(inc, IncidentPath.EndingNode);

                int FromCarToStart = (int)Node.GetDistance(CarsPosition, CarPath.StatingNode);
                int FromCarToEnd = (int)Node.GetDistance(CarsPosition, CarPath.EndingNode);

                string str = "EXEC [dbo].[ShortestPath]" +
                    " @IncPathStart = N'" + IncidentPath.StatingNode.ID.ToString() +
                    "', @IncPathEnd = N'" + IncidentPath.EndingNode.ID.ToString() +
                    "', @CarPathStart = N'" + CarPath.StatingNode.ID.ToString() +
                    "', @CarPathEnd = N'" + CarPath.EndingNode.ID.ToString() +
                    "', @FromIncToStart = " + FromIncToStart.ToString() +
                    ", @FromIncToEnd = " + FromIncToEnd.ToString() +
                    ", @FromCarToStart = " + FromCarToStart.ToString() +
                    ", @FromCarToEnd = " + FromCarToEnd.ToString();
                this._sCom.CommandText = str;
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();

                List<int> li = new List<int>();
                List<int> li2 = new List<int>();
                while (this._sdr.Read())
                {
                    li.Add(Convert.ToInt32(this._sdr.GetString(0)));
                    li2.Add(Convert.ToInt32(this._sdr.GetString(1)));
                }
                this._sdr.Close();
                this._sCon.Close();

                int count = li.Count;
                arr = new Node[count + 2];
                for (int i = 0; i < count; i++)
                    arr[i] = LoadNode(li[i]);

                arr[count] = LoadNode(li2[count - 1]);
                arr[count + 1] = inc;
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return arr;
        }
        public Node GetPosition(Address add)
        {
            Node res = null;
            int b = add.Number;
            int startBuilding, finishBuilding, pieceID, startingNodeID, endingNodeID;
            Node st, ed;
            try
            {
                pieceID = GetPathID(add);
                if (pieceID == -1)
                    return res;

                if (b % 2 == 1)
                    this._sCom.CommandText = "SELECT StartingOddBuilding,EndingOddBuilding, FromNodeID, ToNodeID FROM Paths WHERE (PathID = " + pieceID + ")";
                else
                    this._sCom.CommandText = "SELECT StartingEvenBuilding,EndingEvenBuilding, FromNodeID, ToNodeID FROM Paths WHERE (PathID = " + pieceID + ")";
                this._sCon.Open();
                this._sdr = this._sCom.ExecuteReader();
                this._sdr.Read();
                startBuilding = this._sdr.GetInt32(0);
                finishBuilding = this._sdr.GetInt32(1);
                startingNodeID = this._sdr.GetInt32(2);
                endingNodeID = this._sdr.GetInt32(3);
                this._sdr.Close();
                this._sCon.Close();

                st = LoadNode(startingNodeID);
                ed = LoadNode(endingNodeID);

                double sl = Math.Abs((double)(b - startBuilding) / (double)(startBuilding - finishBuilding));

                int resX = (int)(sl * (ed.X - st.X) + st.X);
                int resY = (int)(sl * (ed.Y - st.Y) + st.Y);

                try
                {
                    double slope = (st.X - ed.X) / ((double)st.Y - ed.Y);

                    if ((resX + 1 - ed.X) == slope * (resY - ed.Y))
                    {
                        resX++;
                    }
                    else if ((resX - ed.X) == slope * (resY + 1 - ed.Y))
                    {
                        resY++;
                    }
                }
                catch (Exception)
                {
                }
                res = new Node(resX, resY);
            }
            catch (Exception e)
            {
                LogError(e.Message + "---" + e.StackTrace);
            }
            finally
            {
                try { this._sdr.Close(); }
                catch (Exception) { }
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
            return res;

        }
        #endregion

        #region PrivateMethods
        private Incident ParseIncident(SqlDataReader sdr)
        {
            Address a = new Address(sdr.GetInt32(4), sdr.GetString(5), sdr.GetString(6));
            DateTime repDate = sdr.GetDateTime(13);
            DateTime dt = sdr.GetDateTime(12);
            Incident ic = new Incident(sdr.GetInt32(0), sdr.GetBoolean(1), sdr.GetInt32(2), sdr.GetInt32(8), sdr.GetInt32(9), sdr.GetBoolean(3), a, sdr.GetString(10), sdr.GetInt64(11), sdr.GetString(7), repDate, dt);
            ic.Coverd = sdr.GetBoolean(14);
            return ic;
        }
        private void LogError(string ErrorMessage)
        {
            try
            {
                if (!this._sdr.IsClosed)
                    this._sdr.Close();
                if (this._sCon.State != System.Data.ConnectionState.Closed)
                    this._sCon.Close();
                this._sCom.CommandText = "INSERT INTO ErrorLog(Data, Date) VALUES ('" + ErrorMessage + "', @date)";
                this._sCom.Parameters.AddWithValue("@date", DateTime.Now);
                this._sCon.Open();
                this._sCom.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }
            finally
            {
                try { this._sCon.Close(); }
                catch (Exception) { }
            }
        }
        #endregion
    }
}
